package com.xbts.screen.mapper;

import com.xbts.common.core.web.domain.BasePieChartResult;
import com.xbts.screen.domain.dto.HistogramDto;
import com.xbts.screen.domain.vo.WorkshopPassRateRecordVO;
import com.xbts.screen.domain.workshop.dto.CommonQueryDTO;
import com.xbts.screen.domain.workshop.vo.SamplingPassRateTrendVO;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * 物料品质
 */
public interface MaterialQualityMapper {


    @Select("SELECT CAST(SUM(ISNULL(DATA0911.QUAN_REJD,0)) AS INT) value,data0911.remark type\n" +
            "FROM DATA0908\n" +
            " inner join data0909 a0 on DATA0908.rkey=a0.rc_ptr and a0.quantity>0\n" +
            " left join data0910 on data0910.d0909_ptr=a0.rkey\n" +
            " left join data0911 on data0911.d0910_ptr=data0910.rkey \n" +
            " left join data0017 on data0017.rkey=a0.INVENTORY_PTR\n" +
            " left join data0023 on data0908.supp_ptr=data0023.rkey \n" +
            "GROUP BY data0911.remark\n" +
            "HAVING SUM(ISNULL(DATA0911.QUAN_REJD,0))>0 ORDER BY value DESC")
    List<BasePieChartResult> samplingBadStatistics();

    @Select("SELECT\n" +
            "DATA0023.ABBR_NAME,SUM(ISNULL(DATA0911.QUAN_REJD,0)) 不良数量,SUM(data0910.quantity) 检验数量,\n" +
            "data0017.INV_PART_NUMBER,data0017.INV_NAME,data0017.INV_PART_DESCRIPTION,DATA0910.create_date\n" +
            "INTO #TEMP\n" +
            "FROM DATA0908\n" +
            " inner join data0909 a0 on DATA0908.rkey=a0.rc_ptr and a0.quantity>0\n" +
            " left join data0910 on data0910.d0909_ptr=a0.rkey\n" +
            " left join data0911 on data0911.d0910_ptr=data0910.rkey \n" +
            " left join data0017 on data0017.rkey=a0.INVENTORY_PTR\n" +
            " left join data0023 on data0908.supp_ptr=data0023.rkey \n" +
            "\n" +
            "WHERE DATA0910.create_date>=#{startTime}  and DATA0910.create_date<=#{endTime}\n" +
            "\n" +
            "GROUP BY ABBR_NAME,data0017.INV_PART_NUMBER,data0017.INV_NAME,data0017.INV_PART_DESCRIPTION,DATA0910.create_date\n" +
            "\n" +
            "SELECT TOP 10  ABBR_NAME supplier,  INV_PART_NUMBER itemNumber ,INV_NAME 材料名称,INV_PART_DESCRIPTION 材料规格,ROUND(1-CAST(不良数量 AS FLOAT)/CAST(检验数量 AS FLOAT),10) * 100 passRate  FROM #TEMP ORDER BY create_date desc\n" +
            "DROP TABLE #TEMP")
    List<WorkshopPassRateRecordVO> passRateRecord(@Param("startTime") String startTime, @Param("endTime") String endTime);

    @Select("SELECT CAST(SUM(DATA0911.QUAN_REJD) AS INT) actual, CONVERT(varchar(100),DATA0910.create_date ,23) temp ,CAST(SUM(data0910.quantity) AS INT) target\n" +
            "FROM DATA0908\n" +
            " inner join data0909 a0 on DATA0908.rkey=a0.rc_ptr and a0.quantity>0\n" +
            " left join data0910 on data0910.d0909_ptr=a0.rkey\n" +
            " left join data0911 on data0911.d0910_ptr=data0910.rkey \n" +
            " left join data0017 on data0017.rkey=a0.INVENTORY_PTR\n" +
            " left join data0023 on data0908.supp_ptr=data0023.rkey \n" +
            "WHERE DATA0910.create_date>=#{startTime}  and DATA0910.create_date<=#{endTime} and DATA0911.QUAN_REJD > 0\n" +
            "GROUP BY DATA0910.create_date\n" +
            "ORDER BY DATA0910.create_date  asc")
    List<HistogramDto> samplingPassRateTrend(@Param("startTime") String startTime, @Param("endTime") String endTime);

    SamplingPassRateTrendVO materialTypeSampling(CommonQueryDTO dto);
}
